# -*- coding: utf-8 -*-
#########################################################################
## This scaffolding model makes your app work on Google App Engine too
#########################################################################
from datetime import datetime, timedelta
try:
    from gluon.contrib.gql import *  # if running on Google App Engine
except:
    db = SQLDB('sqlite://storage.db')  # if not, use SQLite or other DB
else:
    db = GQLDB()  # connect to Google BigTable
    session.connect(request, response, db=db)  # and store sessions there
    # or use the following lines to store sessions in Memcache
    #from gluon.contrib.memdb import MEMDB
    #from google.appengine.api.memcache import Client
    #session.connect(request, response, db=MEMDB(Client()))

#########################################################################
## uncomment the following line if you do not want sessions
#session.forget()
#########################################################################

from gluon.tools import Mail, Auth, Crud     # new in web2py 1.56
mail=Mail()                                  # mailer
mail.settings.server='smtp.gmail.com:587'    # your SMTP server
mail.settings.sender='trac@cat-hackers.net'         # your email
mail.settings.login='trac@cat-hackers.net:tracpassword'      # your credentials
auth=Auth(globals(),db)                      # authentication/authorization
auth.settings.mailer=mail                    # for user email verification



"""
Table definition
"""
db.define_table("categories",
      SQLField("name", "string", required=True, unique=True, requires=IS_NOT_IN_DB(db, 'categories.name')),
      SQLField("description", "text"))


"""
Table definition
"""
db.define_table("foundations",
      SQLField("name", "string", required=True, unique=True, requires=IS_NOT_IN_DB(db, 'foundations.name')),
      SQLField("description", "text"))


"""
Table definition
"""
auth.settings.table_user_name = "users"

auth.settings.table_user = db.define_table(auth.settings.table_user_name,
      SQLField("username", "string", label="Username", required=True, unique=True, requires=[IS_NOT_IN_DB(db, '%s.username' % auth.settings.table_user_name), IS_NOT_IN_DB(db, '%s.email' % auth.settings.table_user_name)]),
      SQLField("password", "password", label="Password", required=True, length=100, readable=False, requires=[CRYPT(), IS_MATCH('\w{5,100}')]),
      SQLField("first_name", "string", label="First Name", length=255, required=True),
      SQLField("last_name", "string", label="Last Name", length=255, required=True),
      SQLField("email", "string", label="E-Mail", length=255, required=True, requires=[IS_NOT_IN_DB(db, '%s.email' % auth.settings.table_user_name), IS_EMAIL()]),
      SQLField("mobile", "string", label="Mobile Phone", length=12, required=True),
      SQLField("faculty", "string", label="Faculty", length=255),
      SQLField("department", "string", label="Department", length=255),
      SQLField("grade", "string", label="Faculty Grade"),
      SQLField("gender", "string", required=True, requires=IS_IN_SET(['Male', 'Female'])),
      SQLField("foundationID", db.foundations, label="CAT Foundation"),
      SQLField("avatar", "upload", required=False),
      SQLField("isValid", "boolean", readable=False, writable=False, default=True),
      SQLField('registration_key', 'string', length=128, writable=False, readable=False, default=''),
      SQLField("registeredOn", "date",writable=False, readable=True, label="Registration Date", default=datetime.today(), required=True, requires=IS_DATE()),
      SQLField("expiryDate", "date", label="Expiration Date", comment="Account will expire by default after 3 days of registration",
                  writable=False, default=datetime.today()+timedelta(days=3), required=True, requires=IS_DATE()))

"""
Table definition
"""
db.define_table("generalLedger",
      SQLField("amountIn", "integer", default=0, required=True),
      SQLField("amountOut", "integer", default=0, required=True),
      SQLField("description", "text", required=True),
      SQLField("transactionOwnerID", db.users),
      SQLField("datetime", "datetime", default=datetime.today()))
      
"""
Table definition
"""
db.define_table("log",
      SQLField("datetime", "datetime", default=datetime.today(), required=True),
      SQLField("byUser", db.users),
      SQLField("onWhat", "string", default='BOOK', required=True, requires=IS_IN_SET(['BOOK', 'USER'])),
      SQLField("onWho", "integer", required=True),
      SQLField("operation", "string", required=True, requires=IS_IN_SET(['REQUEST', 'COMMENT', 'BORROW', 'ADD', 'REMOVE', 'MODIFY', 'CANCEL'])))
      
"""
Table definition
"""
db.define_table("books",
      SQLField("title", "string", required=True, unique=True, length=255, requires=[IS_NOT_IN_DB(db, 'books.title')]),
      SQLField("owner", db.users),
      SQLField("submissionDate", "date", required=True, default=datetime.today()),
      SQLField("rate", "integer",required=True, default=1, requires=IS_INT_IN_RANGE(0,10)),
      SQLField("ISBN", "string",required=False),
      SQLField("numberOfPages", "integer", required=False),
      SQLField("author", "string", length=255, required=True),
      SQLField("description", "text", required=True),
      SQLField("categoryID", db.categories),
      SQLField("photo", "upload", required=False),
      SQLField("currentlyWith", db.users),
      SQLField("status", "string", required=True, requires=IS_IN_SET(['Available', 'Borrowed']), default='Available'))
      
"""
Table definition
"""
db.define_table("readRequests",
      SQLField("bookID", db.books),
      SQLField("requestBy", db.users),
      SQLField("requestedOn", "datetime", default=datetime.today(), required=True))

"""
Table definition
"""
db.define_table("reviews",
      SQLField("bookID", db.books),
      SQLField("review", "text", required=True),
      SQLField("author", db.users),
      SQLField("submissionDate", "datetime", default=datetime.today()),
      SQLField("subject", "string"))

      
"""
Relations between tables (remove fields you don't need from requires)
"""
db.users.foundationID.requires=IS_IN_DB(db, 'foundations.id','foundations.name')
db.books.owner.requires=IS_IN_DB(db, 'users.id','users.username')
db.books.categoryID.requires=IS_IN_DB(db, 'categories.id','categories.name')
db.books.currentlyWith.requires=IS_IN_DB(db, 'users.id','users.username')
db.reviews.bookID.requires=IS_IN_DB(db, 'books.id','books.title')
db.reviews.author.requires=IS_IN_DB(db, 'users.id','users.username')
db.readRequests.bookID.requires=IS_IN_DB(db, 'books.id','books.title')
db.readRequests.requestBy.requires=IS_IN_DB(db, 'users.id','users.username')
db.log.byUser.requires=IS_IN_DB(db, 'users.id','users.username')
db.generalLedger.transactionOwnerID.requires=IS_IN_DB(db, 'users.id','users.username')

auth.define_tables()                         # creates all needed tables
crud=Crud(globals(),db)                      # for CRUD helpers using auth
crud.settings.auth=auth                      # (optional) enforces authorization on crud
auth.settings.login_url = URL(r=request, c='default',
                f='user', args='login')

#########################################################################
## Define your tables below, for example
##
## >>> db.define_table('mytable',SQLField('myfield','string'))
##
## Fields can be 'string','text','password','integer','double','booelan'
##       'date','time','datetime','blob','upload', 'reference TABLENAME'
## There is an implicit 'id integer autoincrement' field
## Consult manual for more options, validators, etc.
##
## More API examples for controllers:
##
## >>> db.mytable.insert(myfield='value')
## >>> rows=db(db.mytbale.myfield=='value).select(db.mytable.ALL)
## >>> for row in rows: print row.id, row.myfield
#########################################################################

#########################################################################
## Here is sample code if you need:
## - email capabilities
## - authentication (registration, login, logout, ... )
## - authorization (role based authorization)
## - crud actions
## uncomment as needed
#########################################################################


#########################################################################
## then, to expose authentication
## http://..../[app]/default/user/login
## http://..../[app]/default/user/logout
## http://..../[app]/default/user/register
## http://..../[app]/default/user/profile
## http://..../[app]/default/user/retrieve_password
## http://..../[app]/default/user/change_password
## use the following action in controller default.py
##
##     def user(): return dict(form=auth())
##
## read docs for howto create roles/groups, assign memberships and permissions
##
## to expose CRUD
## http://..../[app]/default/data/tables
## http://..../[app]/default/data/select/[table]
## http://..../[app]/default/data/create/[table]
## http://..../[app]/default/data/read/[table]/[id]
## http://..../[app]/default/data/update/[table]/[id]
## http://..../[app]/default/data/delete/[table]/[id]
## use the following action in controller default.py
##
##     def data(): return dict(form=crud())
##
## to allow automatic download of all uploaded files and enforce authorization
## use the following action in controller default.py
##
##     def download(): return response.download(request,db)
